﻿/*  
 *  IIS LogFile Analyser, Copyright (C) 2013. http://www.iis-analyser.com
 *  --
 *  This program is free software, provided "AS IS" WITHOUT ANY WARRANTY; without even the implied warranty of 
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE
 * 
 *  File Author: Ian Quigley,    2013-02-23
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace DataStore
{
    public class ViewRecord
    {
        public long Id { get; set; }

        public string Name;
        public string Filters;
        public string Sql;
        public string DisplayView;
        public bool InMenu;
        
        internal static void InitialiseTable()
        {
            bool tableExists;
            using (SQLiteConnection connection = new SQLiteConnection( Database.DataSource ))
            using (SQLiteCommand command = new SQLiteCommand( "SELECT * FROM sqlite_master WHERE Name = 'View' ", connection ))
            {
                connection.Open();
                var reader = command.ExecuteReader();
                tableExists = reader.Read();
                connection.Close();
            }

            if (tableExists)
                return;

            using (SQLiteConnection connection = new SQLiteConnection( Database.DataSource ))
            using (SQLiteCommand command = new SQLiteCommand( "CREATE TABLE if not exists View (Id Integer PRIMARY KEY AutoIncrement, Name text, Filters text, SQL text, DisplayView text, InMenu bit)", connection ))
            {
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }

            // Table just created, so populate with some basic data            
            ViewRecord view = new ViewRecord();
            view.Name = "Top 100 User Agents";
            view.Sql = "select distinct count(ua.name) as count, ua.name from visit v join useragent ua on ua.id = v.UserAgentId where {v.siteid} group by ua.name order by 1 desc limit 100";
            view.DisplayView = "Simple Grid View";
            view.Filters = "FilterSite(v.siteId)";
			view.InMenu = true;
            view.Save();

            view = new ViewRecord();
            view.Id = 0;
            view.Name = "Top 404 Errors";
            view.Sql = "select count(uri.name), s.name , uri.name from visit v join uri on uri.id = v.uriid join site s on s.id = v.siteid where {v.siteid} and v.status=404 group by uri.name, s.name order by 1 desc";
            view.DisplayView = "Simple Grid View";
            view.Filters = "FilterSite(v.siteid)";
            view.InMenu = true;
            view.Save();
        }

        public void Save()
        {
            using (SQLiteConnection connection = new SQLiteConnection( Database.DataSource ))
            using (SQLiteCommand command = new SQLiteCommand( "INSERT OR REPLACE INTO View (" + (Id == 0 ? "" : "Id, ") + "Name, Filters, SQL, DisplayView, InMenu) VALUES (" + (Id == 0 ? "" : "@Id, ") + "@Name, @Filters, @SQL, @DisplayView, @InMenu); select last_insert_rowid();", connection ))
            {
                if (Id != 0)
                    command.Parameters.AddWithValue( "@Id", Id );

                command.Parameters.AddWithValue( "@Name", Name );
                command.Parameters.AddWithValue( "@Filters", Filters);
                command.Parameters.AddWithValue( "@SQL", Sql);
                command.Parameters.AddWithValue( "@DisplayView", DisplayView );
                command.Parameters.AddWithValue( "@InMenu", InMenu);
                
                connection.Open();
                Id = (long)command.ExecuteScalar();
                connection.Close();            
            }        
        }

        internal static void Delete( long viewId )
        {
            using (SQLiteConnection connection = new SQLiteConnection( Database.DataSource ))
            using (SQLiteCommand command = new SQLiteCommand( "DELETE FROM View WHERE Id = @id", connection ))
            {
                connection.Open();
                command.Parameters.AddWithValue( "@id", viewId );
                command.ExecuteNonQuery();
                connection.Close();
            }        
        }

        public static ViewRecord Find(long Id)
        {
            ViewRecord view = null;
            using (SQLiteConnection connection = new SQLiteConnection( Database.DataSource ))
            using (SQLiteCommand command = new SQLiteCommand( "SELECT * FROM View WHERE Id = @id", connection ))
            {
                connection.Open();

                command.Parameters.AddWithValue( "@id", Id );
                var reader = command.ExecuteReader();
                if (reader.Read())
                    view = FromReader( reader );

                connection.Close();
            }
            return view;
        }

        public static List<ViewRecord> ReadAll(bool onlyInMenu)
        {
            List<ViewRecord> views = new List<ViewRecord>();

            string where = "";
            if (onlyInMenu)
                where = " WHERE InMenu = 1";

            using (SQLiteConnection connection = new SQLiteConnection( Database.DataSource ))
            using (SQLiteCommand command = new SQLiteCommand( "SELECT * FROM View" + where , connection ))
            {
                connection.Open();
                var reader = command.ExecuteReader();
                while (reader.Read())
                    views.Add( FromReader( reader ) );

                connection.Close();
            }
            return views;
        }

        private static ViewRecord FromReader( SQLiteDataReader reader )
        {
            var viewRecord = new ViewRecord();
            viewRecord.Id = (long)reader["Id"];
            viewRecord.Name = (string)reader["Name"];
            viewRecord.Sql = (string)reader["SQL"];
            viewRecord.DisplayView = (string)reader["DisplayView"];
            viewRecord.Filters = (string)reader["Filters"];
            viewRecord.InMenu = (bool)reader["InMenu"];
            return viewRecord;
        }



        
    }
}
